package merlin.managers;

import java.io.BufferedInputStream;
import java.io.BufferedWriter;
import java.io.ByteArrayOutputStream;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.OutputStream;
import java.io.OutputStreamWriter;
import java.sql.Connection;
import java.text.SimpleDateFormat;
import java.util.Arrays;
import java.util.LinkedList;
import java.util.List;
import java.util.Map;
import java.util.concurrent.Executors;
import java.util.concurrent.Future;
import java.util.zip.ZipEntry;
import java.util.zip.ZipOutputStream;

import javax.servlet.http.HttpSession;

import merlin.beans.CompanyBusinessDetailsBean;
import merlin.beans.CustomerDetailsBean;
import merlin.beans.LookupDetailsBean;
import merlin.beans.ReportTitlesBean;
import merlin.beans.WebsiteOrdersBean;
import merlin.dao.ConnectionPool;
import merlin.dao.DbResultSet;
import merlin.dao.DirectorDetailsDAO;
import merlin.dao.IndustryWatchDAO;
import merlin.dao.LookupDetailsDAO;
import merlin.dao.MsalResearchersDAO;
import merlin.dao.ReportTitlesDAO;
import merlin.dao.WebsiteOrdersDAO;
import merlin.dao.WebsiteReportOrdersDAO;
import merlin.utils.ApplicationResources;
import merlin.utils.MailSender;
import merlin.utils.MerlinUtilities;

public class BibServiceManager {

	private static int maxRecords;
	
	static{
		ApplicationResources resource = ApplicationResources.getInstance();
		try{
			String str = resource.getProperty("search.maxselect");
        	if(str != null)maxRecords = Integer.parseInt(str);
		}catch(Exception e){
			e.printStackTrace();
		}
	}
	
	public static CustomerDetailsBean login(String email, String password){
		CustomerDetailsBean bean = null;
		Connection con=null;
		ConnectionPool pool = null;
		try{
			pool = ConnectionPool.getConnectionPool();
			con = pool.getConnection();

			MsalResearchersDAO dao = new MsalResearchersDAO(con);
			bean = dao.loginForBibService(email, password);
		}catch(Exception e){
			e.printStackTrace();
			throw new RuntimeException(e);
		}finally{
			pool.returnConnection(con);
			con=null;
		}
		return bean;
	}
	
	// Report searches.
	public static DbResultSet searchReports(String searchText){
		ConnectionPool pool = null;
		Connection con=null;
		DbResultSet result = new DbResultSet();
		try{
			pool = ConnectionPool.getConnectionPool();
			con = pool.getConnection();

			ReportTitlesDAO dao = new ReportTitlesDAO(con);

			String sqlWhereClause= " SATISFIES(NSD.IDX_REPORT_DESCRIPTION,'" + searchText + "') ";
			result = dao.selectReportTitlesRecords(sqlWhereClause,"REPORT_TITLE");
		}catch(Exception e){
			System.err.println(e);
			throw new RuntimeException(e);
		}finally{
			pool.returnConnection(con);
			con=null;
		}
		return result;
	}
	
	/**
	 * Search for reports in which the given company occurs.
	 * Don't pull the custom reports in the search. 
	 * 
	 * @param searchText
	 * @return
	 */
	public static DbResultSet searchReportsByCompanyId(String companyNumber){
		ConnectionPool pool = null;
		Connection con=null;
		DbResultSet result = new DbResultSet();
		try{
			pool = ConnectionPool.getConnectionPool();
			con = pool.getConnection();

			ReportTitlesDAO dao = new ReportTitlesDAO(con);

			String sqlWhereClause= " REPORT_TITLES_ID IN (SELECT MSAL_R_REPORT_NUMBER FROM MSAL_REPORTS WHERE MSAL_R_REGISTERED_NUMBER =  '" + companyNumber + "') AND CUSTOMISED_INDICATOR IS NULL ";
			result = dao.selectReportTitlesRecords(sqlWhereClause,"REPORT_TITLE");
		}catch(Exception e){
			System.err.println(e);
			throw new RuntimeException(e);
		}finally{
			pool.returnConnection(con);
			con=null;
		}
		return result;
	}

	
	public static DbResultSet searchCompanies(String searchString, boolean alphaSearch){
		String whereClause = null;
		if(alphaSearch){
			searchString = MerlinUtilities.cleanStringForAlphakeySearch(searchString);
			if(searchString.length() > 0)whereClause= " ALPHAKEY_COMPANY_NAME LIKE '" + searchString + "%' ";
		}else{
			// Keyword based search - clean the search string
	     	searchString = MerlinUtilities.parseSearchString(searchString,"&");
			if(searchString.length() > 0)whereClause= " SATISFIES(NSD.IDX_COMPANY_DATA,'" + searchString + "') ";
    	}

		// the order by clause
		String orderBy = "COMPANY_NAME";

		DbResultSet result  = LookupDetailsManager.selectTopLookupDetailsByKeywordRecords(whereClause,orderBy,maxRecords) ;
		return result;
	}

	public static CompanyBusinessDetailsBean getCompanyDetails(String companyNumber){
		LookupDetailsBean ldBean  = LookupDetailsManager.selectLookupDetailsByPK(companyNumber) ;

		if(ldBean != null){
			// if some record was found, retrieve the details of the record.			
			CompanyBusinessDetailsBean bean = new CompanyBusinessDetailsBean();
			bean.setCompanyDetails(ldBean);
			String companyId = ldBean.getCompanyNumber();
   			bean.setShareholderDetails(ShareholderDetailsManager.selectShareholderDetailsRecordsByCompanyNumber(companyId));
			bean.setDirectorDetails(DirectorDetailsManager.selectDirectorDetailsRecordsByCompanyNumber(companyId));
			bean.setParentSubsidiaryDetails(ParentSubsidiaryDetailsManager.selectParentSubsidiaryDetailsRecordsByCompanyNumber(companyId));
			bean.setFinancialDetails(FinancialDetailsManager.selectFinancialDetailsRecordsByCompanyNumber(companyId));
			return bean;
		}else{		
			return null;
		}
	}

	public static DbResultSet searchCompanyByDirector(String searchString){
		return LookupDetailsManager.selectLookupDetailsRecordsByDirector(searchString);
	}
	
	public static DbResultSet searchCompanyByParent(String parentNumber){
		DbResultSet result = new DbResultSet();
		LookupDetailsBean ldBean  = LookupDetailsManager.selectLookupDetailsByPK(parentNumber) ;

		if(ldBean != null){
			result.add(ldBean);
		}
		return result;
	}
	
	
	public static void sendMailToAddCompaniesToReports(HttpSession session, ReportTitlesBean[] reports, LookupDetailsBean[] companiesToAdd){
		
		String mailFrom     = (String)session.getAttribute("username");
		
		StringBuilder sbMessage = new StringBuilder();
		StringBuilder sbAttachment = new StringBuilder();

  		sbMessage.append("<font face=\'verdana\'>");
		sbMessage.append("E-Mail Address : ").append(mailFrom).append("<BR>");
		sbMessage.append("<HR>");
		for(int i=0;i<reports.length;i++){
			sbMessage.append("<BR><FONT COLOR=\'BLUE\'>Report ID : ").append(reports[i].getReportTitlesId()).append("<BR>");
			sbMessage.append("<B>Report Title : ").append(reports[i].getReportTitle()).append("</FONT></B><P>");
		}
		// Companies to add

		if(companiesToAdd.length > 0){
			sbMessage.append("<font color=\'green'>");
			sbMessage.append("<HR><B>Companies to Add</B><HR>");
  			for(int i=0;i<companiesToAdd.length;i+=2){
	  			sbMessage.append("<BR>Company Number : ").append(companiesToAdd[i].getCompanyNumber());	// Company number.
	  			sbMessage.append("<BR><B>Company Name   : ").append(companiesToAdd[i].getCompanyName()).append("</B>");	// Company name.
	  			sbMessage.append("<BR>");

	  			sbAttachment.append(companiesToAdd[i].getCompanyNumber()).append(",XXX\r\n");
	  		}
	  		sbMessage.append("<P><PRE>").append(sbAttachment).append("</PRE>");
		  	sbMessage.append("</font>");
  		}

		ApplicationResources resource = ApplicationResources.getInstance();
		String mailTo  = resource.getProperty("mail.to");

		String subject = resource.getProperty("mail.subject.freeupdates");
		
		MailSender.sendMail(mailFrom,mailTo,subject,sbMessage.toString());
	}
	
	public static void sendReportDownloadEmail(HttpSession session, ReportTitlesBean[] reports){
		Connection con=null;
		ConnectionPool pool = null;

		Exception ex = null;
		int orderId = -1;

		Map mapResponse = null;
		try{
	        ApplicationResources resource = ApplicationResources.getInstance();

			int arrReportIds[] = new int[reports.length];

			for(int i=0,j=0;i<arrReportIds.length;i++,j+=2){
				arrReportIds[i] = reports[i].getReportTitlesId();
			}
			
			CustomerDetailsBean bean = (CustomerDetailsBean)session.getAttribute("userDetails");
			String mailFrom     = bean.getEmailAddress();
			
			WebsiteOrdersBean woBean = new WebsiteOrdersBean(
					resource.getProperty("bib.companyName"),
					resource.getProperty("bib.addressLine1"),
					resource.getProperty("bib.addressLine2"),
					resource.getProperty("bib.town"),
					resource.getProperty("bib.county"),
					resource.getProperty("bib.postcode"),
					resource.getProperty("bib.telephone"),
					mailFrom,
					bean.getCustomerDetailsId()
			);

			pool = ConnectionPool.getConnectionPool();
			con = pool.getConnection();

			con.setAutoCommit(false);
			WebsiteOrdersDAO wedao = new WebsiteOrdersDAO(con);
			orderId = wedao.addWebsiteOrder(woBean);
			System.out.println("added WebsiteOrder");

			WebsiteReportOrdersDAO wrrdao = new WebsiteReportOrdersDAO(con);
			int arr[] = wrrdao.addWebsiteReportOrders(orderId,arrReportIds);
			
			IndustryWatchDAO iwdao = new IndustryWatchDAO(con);
			String[] uuids = iwdao.addMerlinEBookDownloadHistory(orderId,arrReportIds);
			System.out.println("Added Merlin reports");
			con.commit();
			sendMailWithReportDownloadLinks(orderId, reports, mailFrom, bean.getDownloadPageUrl());
			
		}catch(Exception e){
			e.printStackTrace();
			ex = e;
		}finally{
			pool.returnConnection(con);
			con=null;
		}
		if(ex != null){
			throw new RuntimeException(ex);
		}
	}
	
	private static void sendMailWithReportDownloadLinks(int orderId, ReportTitlesBean[] reports, String mailFrom, String downloadPageUrl){

    	try{
			ApplicationResources resource = ApplicationResources.getInstance();

			// send a mail to the administrator.
			StringBuilder sbMessageMSAL = new StringBuilder("The following eBooks were requested:<p/>");
			StringBuilder sbMessageCustomer = new StringBuilder();

			for(int i=0;i<reports.length;i++){
			  		sbMessageMSAL.append("<BR>" + (i + 1) + ". " + reports[i].getReportTitle());
			  		sbMessageCustomer.append("<BR>" + reports[i].getReportTitlesId() + " : " + reports[i].getReportTitle());
	  		}

			// The download URL is part of the customer's profile data stored in the DB. If it is not set, use the default URL.
			String downloadLinkForEBooks = null;
			if(downloadPageUrl != null && downloadPageUrl.length() > 0){
				downloadLinkForEBooks = downloadPageUrl;
			}else{
				downloadLinkForEBooks = resource.getProperty("merlinBaseURL") + "/mr_downloadEBooks.jsp";
			}
			downloadLinkForEBooks += "?token=" + Integer.toHexString(orderId).toUpperCase() + "&service=bib";
			
			
			System.out.println(downloadLinkForEBooks);
			sbMessageMSAL.append("<P>These eBooks are obtained from the <a href ='").append(downloadLinkForEBooks).append("'>").append("report download page").append("</a>");
	  		sbMessageCustomer.append("<P>These eBooks are obtained from the <a href ='").append(downloadLinkForEBooks).append("'>").append("report download page").append("</a>");
	  		sbMessageCustomer.append(".  If the report is still being prepared (you will see a message to this effect) simply return to report download page later,");
	  		sbMessageCustomer.append(" the new report will have been compiled and will be available for you to download.<P/>");

			sbMessageCustomer.append("The report file size can be up to 56MB so it will take up to five ");
			sbMessageCustomer.append("minutes depending on your internet connection speed to successfully ");
			sbMessageCustomer.append("download.  Settings you have got in your browser will determine whether ");
			sbMessageCustomer.append("the download manager shows you the progress of the file transfer or the ");
			sbMessageCustomer.append("browser window may just stay blank until the file is downloaded ");
			sbMessageCustomer.append("completely,  please be patient while this process is underway.  You ");
			sbMessageCustomer.append("should save a copy of the report file on your hard disk once the ");
			sbMessageCustomer.append("download is complete.<p/>");

			sbMessageCustomer.append("You will require a copy of Adobe Acrobat Reader (free from <a href='www.adobe.com'>www.adobe.com</a>) to view the report contents.");


			sbMessageCustomer.append("<br><br>Niel Dalrymple<br>Merlin Scott Associates Limited");

			String mailTo  = resource.getProperty("mail.to");
			String subject =  "eBooks requested from Merlin Scott Associates";

			// Send mail to MSAL
			MailSender.sendMail(mailFrom,mailTo,"eBooks requested from BIB Service",sbMessageMSAL.toString());

			
			mailTo 	 = mailFrom;
			mailFrom = "Niel.Dalrymple@industrywatch.co.uk";
			//Send mail to customer.
			MailSender.sendMail(mailFrom,mailTo,subject,sbMessageCustomer.toString());
		}catch(Exception e){
			e.printStackTrace();
		}
	}
	
	@SuppressWarnings("unchecked")
	public static Map getDirectorSections(){
		Connection con=null;
		ConnectionPool pool = null;


		Map directorSections = null;
		try{
			pool = ConnectionPool.getConnectionPool("researcher");
			con = pool.getConnection();
			DirectorDetailsDAO dao = new DirectorDetailsDAO(con);
			directorSections = dao.getDirectorSections();
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			pool.returnConnection(con);
			con=null;
		}
		return directorSections;
	}
	
	public static void generateMailingList(String[] companyIds, int addressType, int[] directorSortOptions, String reportNamesUsed, HttpSession session){
		CustomerDetailsBean customerDetailsBean  = (CustomerDetailsBean)session.getAttribute("userDetails");
		 
		BibServiceMailingListGenerator mailGenerator = new BibServiceMailingListGenerator(customerDetailsBean, companyIds, addressType, directorSortOptions, reportNamesUsed);
		Future<Boolean> future= Executors.newSingleThreadExecutor().submit(mailGenerator);
		System.out.println(future);
	}

	public static void addToMailingListExclusionList(String[] companyIds, HttpSession session){
		String userCompanyNumber = ((CustomerDetailsBean)session.getAttribute("userDetails")).getCompanyNumber();
		// remove companies that are already added to this user's exclusion list.
		List<String> companiesToAdd = new LinkedList<String>(Arrays.asList(companyIds));
		
		Connection con=null;
		ConnectionPool pool = null;

		try{
			pool = ConnectionPool.getConnectionPool("researcher");
			con = pool.getConnection();
			LookupDetailsDAO dao = new LookupDetailsDAO(con);
			
			// remove duplicate records.
			companiesToAdd.removeAll(dao.getCompanyIdsInBibMailingListExclusionsList(userCompanyNumber));
			
			// add the remaining companies.
			dao.addCompaniesToMailingListExclusionList(companiesToAdd, userCompanyNumber);
		}catch(Exception e){
			e.printStackTrace();
			throw new RuntimeException();
		}finally{
			pool.returnConnection(con);
			con=null;
		}
	}

	public static void exportMailingListExclusionList(HttpSession session){
		List<LookupDetailsBean> companies = retrieveMailingListExclusionList(session);
		try{
			// store the data in a text file first.
			SimpleDateFormat sdf = new SimpleDateFormat("dd_MMM_yyyy_hh_mm_ss_a");
			String time = sdf.format(new java.util.Date());
			File file = new File(ApplicationResources.getInstance().getProperty("iwSummaryPDFStorageFolder") + "BIBMailingExclusionList-" + time + ".txt");
			System.out.println("Saving records to file " + file);
			OutputStream bOut = new FileOutputStream(file);
			BufferedWriter writer = new BufferedWriter(new OutputStreamWriter(bOut));
			for(LookupDetailsBean bean : companies){
				writer.write(bean.getCompanyNumber() + "\t" + bean.getCompanyName() + "\n");
				writer.flush();
			}
			writer.close();
			bOut.close();
			
			// create the zipped data for email attachment.
			ByteArrayOutputStream zipOut = new ByteArrayOutputStream();
			ZipOutputStream out = new ZipOutputStream(zipOut);
			byte[] data = new byte[1000]; 
			BufferedInputStream in = new BufferedInputStream(new FileInputStream(file));
			int count;
			out.putNextEntry(new ZipEntry("BIBMailingExclusionList-" + time + ".txt"));
			while((count = in.read(data,0,1000)) != -1){  
				out.write(data, 0, count);
			}
			in.close();
			out.flush();
			out.close(); 

			file.delete(); // delete the intermediate text file.
			
			// send email.
			String mailSubject = "Mailing Exclusion List on " + time; 
			String emailBody = mailSubject;
			
			//System.out.println(emailBody);
			
			// send to end user. 
			MailSender.sendMail("Niel.Dalrymple@industrywatch.co.uk", 
					((CustomerDetailsBean)session.getAttribute("userDetails")).getEmailAddress(), 
					mailSubject, emailBody, "text/plain", 
					zipOut.toByteArray(), "application/zip", "BIBMailingList-" + time + ".zip");

		}catch(Exception e){
			e.printStackTrace();
		}
	}

	public static List<LookupDetailsBean> retrieveMailingListExclusionList(HttpSession session){
		Connection con=null;
		ConnectionPool pool = null;


		List<LookupDetailsBean> companies = null;
		try{
			pool = ConnectionPool.getConnectionPool("researcher");
			con = pool.getConnection();
			LookupDetailsDAO dao = new LookupDetailsDAO(con);
			companies = dao.getCompaniesInBibMailingListExclusionsList(((CustomerDetailsBean)session.getAttribute("userDetails")).getCompanyNumber());
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			pool.returnConnection(con);
			con=null;
		}
		return companies;
	}

	public static int deleteMailingListExclusionList(HttpSession session){
		Connection con=null;
		ConnectionPool pool = null;

		int updateCount = 0;
		try{
			pool = ConnectionPool.getConnectionPool("researcher");
			con = pool.getConnection();
			LookupDetailsDAO dao = new LookupDetailsDAO(con);
			updateCount = dao.deleteMailingListExclusionList(((CustomerDetailsBean)session.getAttribute("userDetails")).getCompanyNumber());
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			pool.returnConnection(con);
			con=null;
		}
		return updateCount;		
	}

}
